Skip Headers

Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next

# 113 DBMS_TDB

The DBMS_TDB package reports whether a database can be transported between platforms using the RMAN `CONVERT DATABASE` command. It verifies that databases on the current host platform are of the same endian format as the destination platform, and that the state of the current database does not prevent transport of the database.

See Also: Oracle Database Backup and Recovery Advanced User's Guide regarding database transport using `CONVERT DATABASE`

This chapter contains the following topics:

- Using DBMS_TDB

  - Overview

  - Security Model

  - Constants

  - Views

  - Operational Notes

- Summary of DBMS_TDB Subprograms

## Using DBMS_TDB

This section contains topics which relate to using `DBMS_TDB`.

- Overview

- Constants

- Views

- Operational Notes

### Overview

In many cases, Oracle supports transporting databases between platforms which have the same endian format. However, even when the endian formats are the same, a database must undergo a conversion process to move from one platform to another. There are also preconditions required for the process of transporting a database, such as having the database to be transported open read-only.

The `DBMS_TDB` package serves two purposes:

- Confirming that Oracle supports transporting a database from a given source platform to a given target platform

- Determining whether a database to be transported has been properly prepared for transport, and if not, identifying the condition that prevents database transport

The actual conversion is performed using the Recovery Manager `CONVERT DATABASE` command. For a complete discussion of the requirements for transporting a database, the process of converting a database for transport across platforms, and examples of the use of the DBMS_TDB subprograms in the conversion process, seeOracle Database Backup and Recovery Advanced User's Guide.

### Security Model

Use of this package requires the `DBA` privilege.

### Constants

The DBMS_TDB package defines several enumerated constants that should be used for specifying parameter values. Enumerated constants must be prefixed with the package name, for example, `DBMS_TDB.SKIP_NONE`.

The DBMS_TDB package uses the constants shown in Table 113-1.

Table 113-1 DBMS_TDB Constants

Name | Type | Value | Description
--- | --- | --- | ---
`SKIP_NONE` | `NUMBER` | `0` | Check all files when checking whether a database is ready for transport.
`SKIP_OFFLINE` | `NUMBER` | `2` | Skip files in offline tablespaces when checking whether a database is ready for transport.
`SKIP_READONLY` | `NUMBER` | `3` | Skip files in readonly tablespaces when checking whether a database is ready for transport.

### Views

The `DBMS_TDB` package uses the following view listed in Oracle Database Reference:

- `V$DB_TRANSPORTABLE_PLATFORM`, which specifies which combinations of source and target platforms support database transport.

### Operational Notes

- The subprograms in this package are useful both in determining whether the desired cross-platform database conversion is possible, and in checking whether your database is ready for conversion. See Oracle Database Backup and Recovery Advanced User's Guide for details on the different uses of these subprograms are used in the conversion process.

- The subprograms in this package return simple `TRUE` or `FALSE` results to indicate whether database transport is possible. Use the subprograms with `SERVEROUTPUT` `ON` for informative messages about why transport is not possible.

## Summary of DBMS_TDB Subprograms

Table 113-2 DBMS_TDB Package Subprograms

Subprogram | Description
--- | ---
CHECK_DB Function | Checks whether a database can be transported to a target platform
CHECK_EXTERNAL Function | Checks whether a database has external tables, directory or BFILEs

### CHECK_DB Function

This function checks whether a database can be transported to a target platform. It tests whether transport is supported at all for a given source and destination platform, and whether the database is currently in the correct state for transport.

You can specify whether to skip checking parts of the database that are read-only or offline, if you do not plan to transport them.

The function is overloaded. The different functionality of each form of syntax is presented along with the definition.

Syntax

    DBMS_TDB.CHECK_DB (
         target_platform_name   IN VARCHAR2,
         skip_option            IN  NUMBER)
       RETURN BOOLEAN;

    DBMS_TDB.CHECK_DB (
         target_platform_name   IN VARCHAR2)
       RETURN BOOLEAN;

    DBMS_TDB.CHECK_DB
       RETURN BOOLEAN;

Parameters

Table 113-3 CHECK_DB Procedure Parameters

Parameter | Description
--- | ---
`target_platform_name` | The name of the destination platform, as it appears in `V$DB_TRANSPORTABLE_PLATFORM`
`skip_option` | Specifies which, if any, parts of the database to skip when checking whether the database can be transported. Supported values are listed in Table 113-1, "DBMS_TDB Constants".

Return Values

If the database cannot be transported to the target platform or is not ready to be transported, returns `FALSE`. If the database is ready for transport, returns `TRUE`.

Usage Notes

- If `SERVEROUTPUT` is `ON`, the output will contain the reasons why the database cannot be transported and how to fix the problems. For details on possible reasons and fixes, see Table 113-4, "Reasons for CHECK_DB Function to Return FALSE".

  Table 113-4 Reasons for CHECK_DB Function to Return FALSE

  Cause | Action
  --- | ---
  Unrecognized target platform name. | Check `V$DB_TRANSPORTABLE_PLATFORM` for recognized platform names
  Target platform has a different endian format. | Conversion is not supported
  Database is not open read-only. | Open database read-only and retry
  There are active or in-doubt transactions in the database. | Open the database read-write. After the active transactions are rolled back, open the database read-only and retry. This can happen if users flashback the database and open it read only. The active transactions will be rolled back when the database is opened read-write.
  Deferred transaction rollback needs to be done. | Open the database read-write and bring online the necessary tablespaces. Once the deferred transaction rollback is complete, open the database read-only and retry.
  Database compatibility version is below 10. | Change the init.ora `COMPATIBLE` parameter to 10 or higher, open the database read-only and retry
  Some tablespaces have not been open read-write with compatibility version is 10 or higher. | Change the init.ora `COMPATIBLE` parameter to 10 or higher. Then open the affected tablespaces read-write. Then shut down the database, open it read-only, and retry.

Examples

This example illustrates the use of `CHECK_DB` with a database that is open read-write:

    SQL> SET SERVEROUTPUT ON
    SQL> DECLARE
           db_ready BOOLEAN;
         BEGIN
           db_ready := DBMS_TDB.CHECK_DB('Microsoft Windows IA (32-bit)');
         END;
         /

    Database is not open READ ONLY. Please open database READ ONLY and retry.

    PL/SQL procedure successfully completed.

### CHECK_EXTERNAL Function

This function determines whether a database has external tables, directories or BFILEs.

Syntax

    DBMS_TDB.CHECK_EXTERNAL
       RETURN BOOLEAN;

Return Values

If the database has external tables, directories, or BFILEs, return `TRUE`. Otherwise, return `FALSE`.

Usage Notes

- If `SERVEROUTPUT` is `ON`, the function will output the names of the external tables, directories and BFILEs in the database.

- The database must be open read-write.

Examples

This example illustrates the use of `CHECK_EXTERNAL` with a database that has several external tables, directories and BFILEs:

    SQL> SET SERVEROUTPUT ON
    SQL> DECLARE
            external BOOLEAN;
         BEGIN
           external := DBMS_TDB.CHECK_EXTERNAL;
         END;
         /
    The following external tables exist in the database:
    SH.SALES_TRANSACTIONS_EXT
    The following directories exist in the database:
    SYS.MEDIA_DIR, SYS.DATA_FILE_DIR, SYS.LOG_FILE_DIR, SYS.DATA_PUMP_DIR
    The following BFILEs exist in the database:
    PM.PRINT_MEDIA

    PL/SQL procedure successfully completed.